from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationships
from sqlalchemy.ext.declarative import declarative_base
from person import Person
from sqlalchemy import Column,Table,MetaData,Integer,String,Date,Boolean

Base = declarative_base()
class Person(Base):
    __tablename__='person'
    pno = Column(Integer,primary_key=True,autoincrement=False)
    name = Column(String(20),nullable=True)
    age = Column(Integer)
    address = Column(String(30))


class Boss(Base):
    __tablename__='boss'
    pno = Column(Integer,primary_key=True,autoincrement=False)
    bname = Column(String(20),nullable=True)
    bage = Column(Integer)
    baddress = Column(String(30))


class User(Base):
    __tablename__='user'
    uid = Column(Integer, primary_key=True, autoincrement=False)
    name = Column(String(20), nullable=True)
    age = Column(Integer)
    address = Column(String(30))

class OrmDB(object):
    def __init__(self, username, password, host, port, dbname):
        sql = 'mysql+pymysql://{0}:{1}@{2}:{3}/{4}?charset=utf8'.format(username,password,host,port,dbname)
        engine = create_engine(sql,max_overflow=5)
        Session = sessionmaker(bind=engine)#创建一个sessionmaker对象
        self.session = Session()#创建一个session对象,用来调用内置的增删改查命令

    def addObject(self):#创键对象
        obj = Person()
        obj.pno = 10002
        obj.name = '赵雷'
        obj.age = 22
        obj.address = '小酒馆'
        #添加对象，到指定的数据表中
        self.session.add(obj)
        self.session.commit()

    def findByld(self,id):#按id查询数据
        obj = self.session.query(Person).filter_by(pno=id).first()
        return obj

    def findAll(self):
        ret = self.session.query(Person).all()
        return ret

    def remove(self,id):
        self.session.query(Person).filter_by(pno=id).delete()
        self.session.commit()

    def update_ogj(self,person):
        self.session.query(Person).filter_by(pno=person.pno).update({'name':person.name,'age':person.age,'address':person.address})
        self.session.commit()



    def search(self):
        # ret = self.session.query(Person).filter(Person.age>option)
        ret = self.session.query(Person, Boss).filter(Person.pno == Boss.pno).all()
        return ret


if __name__=='__main__':
    db = OrmDB('root','123456','localhost',3306,'mydb')
    #插入
    # db.addObject()

    #查单条
    # person = db.findByld(10001)
    # print(person.pno,'=====',person.name,'=====',person.age,'====',person.address)

    #查所有
    # items = db.findAll()
    # for person in items:
    #     print(person.pno, '=====', person.name, '=====', person.age, '====', person.address)

    #删除一条
    # db.remove(10001)

    #修改数据
    # person = db.findByld(10002)
    # person.name = '哈姆雷特'
    # person.age = 30
    # person.address = '英国'
    # db.update_ogj(person)

    items = db.search()
    print(items)
    for i in items:
        print(i)
        for user in i:
            print(user)
            # print(user.pno,'======',user.name, '=====', user.age, '====', user.address)


